REM  SQL scripts for Chapter 4 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.



REM  This script will only work in 7.2 and above.
set pagesize 60 linesize 80 newpage 0 feedback off
ttitle skip center "Database File IO Weights" skip center -
"ordered by Drive" skip 2
column Total_IO format 999999999
column Weight format 999.99
column file_name format A40
break on Drive skip 2
compute sum of Weight on Drive

select
substr(DF.Name, 1,5) Drive,
DF.Name File_Name,
FS.Phyblkrd+FS.Phyblkwrt Total_IO,
100*(FS.Phyblkrd+FS.Phyblkwrt)/MaxIO Weight
from V$FILESTAT FS, V$DATAFILE DF,
  (select MAX(Phyblkrd+Phyblkwrt) MaxIO
    from V$FILESTAT)
where DF.File# = FS.File#
order by Weight desc

spool io_weights
/
spool off



alter tablespace TEMP temporary;
alter tablespace TEMP permanent;
alter tablespace DATA coalesce;
alter database
datafile '/db05/oracle/CC1/data01.dbf' resize 200M;
alter tablespace DATA
datafile '/db05/oracle/CC1/data01.dbf' resize 200M;
create tablespace DATA
datafile '/db05/oracle/CC1/data01.dbf' size 200M
autoextend ON
next 10M
maxsize 250M;

alter tablespace DATA
add datafile '/db05/oracle/CC1/data02.dbf'
size 50M
autoextend ON
maxsize unlimited;

alter database
datafile '/db05/oracle/CC1/data01.dbf'
autoextend ON
maxsize unlimited;
> svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;
> mv /db01/oracle/CC1/data01.dbf /db02/oracle/CC1

> svrmgrl
SVRMGR> connect internal;
SVRMGR> startup mount CC1;
SVRMGR> alter database rename file
     2> '/db01/oracle/CC1/data01.dbf' to
     3> '/db02/oracle/CC1/data01.dbf';

SVRMGR> alter database open;

> svrmgrl
SVRMGR> connect internal;
SVRMGR> alter tablespace DATA offline;
SVRMGR> exit;

> mv /db01/oracle/CC1/data01.dbf /db02/oracle/CC1
> svrmgrl
SVRMGR> connect internal;
SVRMGR> alter tablespace DATA rename datafile
     2> '/db01/oracle/CC1/data01.dbf' to
     3> '/db02/oracle/CC1/data01.dbf';

SVRMGR> alter tablespace DATA online;
> svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;

> mv /db05/oracle/CC1/redo01CC1.dbf /db02/oracle/CC1
> svrmgrl
SVRMGR> connect internal;
SVRMGR> startup mount CC1;
SVRMGR> alter database rename file 
     2> '/db05/oracle/CC1/redo01CC1.dbf' to
     3> '/db02/oracle/CC1/redo01CC1.dbf';
SVRMGR> alter database open;

> svrmgrl
SVRMGR> connect internal;
SVRMGR> shutdown;
SVRMGR> exit;
> mv /db05/oracle/CC1/ctrl1CC1.ctl /db02/oracle/CC1
> svrmgrl
SVRMGR> connect internal;
SVRMGR> startup;
SVRMGR> exit;

alter database datafile '/db05/oracle/CC1/data01.dbf' 
resize 80M;


declare
        VAR1 number;
        VAR2 number;
        VAR3 number;
        VAR4 number;
        VAR5 number;
        VAR6 number;
        VAR7 number;
begin
dbms_space.unused_space('OPS$CC1','SPACES','TABLE',
                          VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
   dbms_output.put_line('OBJECT_NAME       = SPACES');
   dbms_output.put_line('---------------------------');
   dbms_output.put_line('TOTAL_BLOCKS      = '||VAR1);
   dbms_output.put_line('TOTAL_BYTES       = '||VAR2);
   dbms_output.put_line('UNUSED_BLOCKS     = '||VAR3);
   dbms_output.put_line('UNUSED_BYTES      = '||VAR4);
   dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||VAR5);
   dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6);
   dbms_output.put_line('LAST_USED_BLOCK   = '||VAR7);
end;
/


alter table SPACES deallocate unused keep 80K;

alter index IU_SPACES$DB_TS_CD rebuild
storage (initial 10M next 5M pctincrease 0)
tablespace INDX_1;



